﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Daycare.Models;
using Daycare.Services;

namespace Daycare.DAL
{
    public class UserRepository
    {
        public UserRepository()
        {
            employees = UpdateEmployeeCollection();
        }

        private List<Employee> UpdateEmployeeCollection()
        {
            DataTable tempTable = new DataTable();
            List<Employee> emps = new List<Employee>();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = new SqlCommand("daycare.GetEmployees", con);
                    sda.Fill(tempTable);
                }
            }
            catch(SqlException)
            {
                return emps;
            }

            foreach (DataRow row in tempTable.Rows)
            {
                Employee emp = new Employee(row["FirstName"].ToString(), row["LastName"].ToString(), row["PhoneNumber"].ToString(), row["EmailAddress"].ToString(), (bool)row["IsEmployed"], row["UserName"].ToString(), (int)row["EmployeeId"], row["MiddleInitial"].ToString());
                emp.employeeImage.imageId = Int32.Parse(row["ImageId"].ToString());
                emp.employeeImage.fileName = row["FileName"].ToString();
                emps.Add(emp);
            }

            return emps;
        }

        public List<Employee> GetEmployees(bool updateCollection)
        {
            if(updateCollection)
            {
                return UpdateEmployeeCollection();
            }
            else
            {
                return employees;
            }
        }


        public bool UpdateEmployee(Employee employee)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.UpdateEmployee", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@EmployeeId", employee.employeeId);
                        comm.Parameters.AddWithValue("@FirstName", employee.firstName);
                        comm.Parameters.AddWithValue("@LastName", employee.lastName);
                        
                        if (employee.phoneNumber == null)
                        {
                            comm.Parameters.AddWithValue("@PhoneNumber", DBNull.Value);
                        }
                        else
                        {
                            comm.Parameters.AddWithValue("@PhoneNumber", employee.phoneNumber);
                        }

                        if (employee.emailAddress == null)
                        {
                            comm.Parameters.AddWithValue("@EmailAddress", DBNull.Value);
                        }
                        else
                        {
                            comm.Parameters.AddWithValue("@EmailAddress", employee.emailAddress);
                        }

                        if (employee.middleInitial == null)
                        {
                            comm.Parameters.AddWithValue("@MiddleInitial", DBNull.Value);
                        }
                        else
                        {
                            comm.Parameters.AddWithValue("@MiddleInitial", employee.middleInitial);
                        }
                        
                        comm.Parameters.AddWithValue("@IsEmployed", employee.isEmployed);
                        comm.Parameters.AddWithValue("@ImageId", employee.employeeImage.imageId);

                        con.Open();
                        return (comm.ExecuteNonQuery() > 0);
                    }
                }
            }
            catch(SqlException)
            {
                return false;
            }
        }

        public Password GetPasswordInfo(String userName)
        {
            DataTable tempTable = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();

                    sda.SelectCommand = new SqlCommand("dbo.GetPasswordInfo", con);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@UserName", userName);
                    sda.Fill(tempTable);
                }
            }
            catch(SqlException)
            {
                return null;
            }

            if (tempTable.Rows.Count > 0)
            {
                return new Password((String)tempTable.Rows[0]["PasswordSalt"], (String)tempTable.Rows[0]["Password"]);
            }
            else
            {
                return null;
            }
        }


        public bool UpdateSalt(String userName, String salt)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("dbo.UpdateSalt", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@UserName", userName);
                        comm.Parameters.AddWithValue("@Salt", salt);

                        con.Open();
                        return (comm.ExecuteNonQuery() > 0);
                    }
                }
            }
            catch(SqlException)
            {
                return false;
            }
        }


        public bool UpdatePassword(String userName, String password)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("dbo.UpdatePassword", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@UserName", userName);
                        comm.Parameters.AddWithValue("@Password", password);

                        con.Open();
                        return (comm.ExecuteNonQuery() > 0);
                    }
                }
            }
            catch(SqlException)
            {
                return false;
            }
        }

        public bool AddEmployee(Employee employee, String pwd, String salt)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.AddEmployee", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@FirstName", employee.firstName);
                        comm.Parameters.AddWithValue("@LastName", employee.lastName);

                        if(employee.phoneNumber == null)
                        {
                            comm.Parameters.AddWithValue("@PhoneNumber", DBNull.Value);
                        }
                        else
                        {
                            comm.Parameters.AddWithValue("@PhoneNumber", employee.phoneNumber);
                        }
                        
                        if(employee.emailAddress == null)
                        {
                            comm.Parameters.AddWithValue("@EmailAddress", DBNull.Value);
                        }
                        else
                        {
                            comm.Parameters.AddWithValue("@EmailAddress", employee.emailAddress);
                        }

                        if (employee.middleInitial == null)
                        {
                            comm.Parameters.AddWithValue("@MiddleInitial", DBNull.Value);
                        }
                        else
                        {
                            comm.Parameters.AddWithValue("@MiddleInitial", employee.middleInitial);
                        }
                        
                        comm.Parameters.AddWithValue("@IsEmployed", employee.isEmployed);
                        comm.Parameters.AddWithValue("@UName", employee.userName);
                        comm.Parameters.AddWithValue("@ImageId", employee.employeeImage.imageId);
                        comm.Parameters.AddWithValue("@Pwd", pwd);
                        comm.Parameters.AddWithValue("@Salt", salt);

                        con.Open();
                        return (comm.ExecuteNonQuery() > 0);
                    }
                }
            }
            catch(SqlException)
            {
                return false;
            }
        }

        public bool AddEmployeeNote(Note note)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.AddEmployeeNote", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@EmployeeId", note.noteOwner.employeeId);
                        comm.Parameters.AddWithValue("@NotedById", note.notedBy.employeeId);
                        comm.Parameters.AddWithValue("@NoteText", note.noteText);

                        con.Open();
                        return (comm.ExecuteNonQuery() > 0);
                    }
                }
            }
            catch (SqlException)
            {
                return false;
            }
        }

        public List<Note> GetEmployeeNotes(Employee emp)
        {
            List<Note> notes = new List<Note>();

            DataTable tempTable = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();

                    sda.SelectCommand = new SqlCommand("daycare.GetEmployeeNotes", con);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@EmployeeId", emp.employeeId);
                    sda.Fill(tempTable);
                }
            }
            catch (SqlException)
            {
                return null;
            }

            if (tempTable.Rows.Count > 0)
            {
                UserService us = new UserService();
               
                foreach(DataRow dr in tempTable.Rows)
                {
                    Note note = new Note();
                    note.noteType = "EmployeeNote";

                    note.noteId = Int32.Parse(dr["EmployeeNoteId"].ToString());
                    note.notedBy = us.GetEmployee(Int32.Parse(dr["NotedById"].ToString()));
                    note.noteOwner = us.GetEmployee(Int32.Parse(dr["EmployeeId"].ToString()));
                    note.noteText = dr["NoteText"].ToString();
                    note.timestamp = DateTime.Parse(dr["Timestamp"].ToString());

                    notes.Add(note);
                }
            }
            else
            {
                return notes;
            }

            return notes;
        }

        private List<Employee> employees = new List<Employee>();
    }
}